package kt.dal;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kt.smpp.pdu.SMSRequest;
import kt.smpp.pdu.SMSResponse;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

public class DAL {
	private static Logger logger = LogManager.getLogger(DAL.class);
	static DBPoolX smsPool = null;
	static DBPoolX smsService = null;
	static {
		try {
			smsPool = DBPoolX.getInstance(DBPoolXName.SMS_DEFAULT);
			smsService = DBPoolX.getInstance(DBPoolXName.SERVICE_DEFAULT);
		} catch (DBPoolNotFoundException e) {
			logger.error("Can not get the pool", e);
			System.out.println("Can not get the pool" + e.toString());
		}
	}
	
	public static Connection getCnn (){
		Connection conn = smsPool.getConnection();
		return conn;
	}

	public static void releaseConnection (Connection conn){
		smsPool.releaseConnection(conn, null);
	}
	
	public static void rollBack(Connection sqlCnn) {
		try {
			sqlCnn.rollback();
		} catch (SQLException e1) {
			logger.error("Can not Rollback", e1);
		}
	}
	
	public static void saveMO(SMSRequest mo) {
		if(logger.isInfoEnabled())
			logger.info("begin save MO :" + mo);
		Connection conn = null;
		PreparedStatement preStmt = null;
		int result = 0;
		try {
			
			conn = smsPool.getConnection();

			String strSQL = "INSERT INTO SMS_RECEIVE_QUEUE (SENDER_NUMBER, SERVICE_NUMBER,  MOBILE_OPERATOR, COMMAND_CODE, INFO, GEN_DATE, REQUEST_ID ) " 
							+ " VALUES(						?, 					?, 				?, 				?, 			?, 		now(), 	  ?)";

			preStmt = conn.prepareStatement(strSQL);
			preStmt.setString(1, mo.getSenderNumber());
			preStmt.setString(2, mo.getServiceNumber());
			preStmt.setString(3, mo.getMobileOperator());
			preStmt.setString(4, mo.getCommandCode());
			preStmt.setString(5, mo.getInfo());
			preStmt.setInt(6, mo.getRequestId());
			result = preStmt.executeUpdate();
		} catch (Exception e) {
			logger.error("Error executing SQL\n", e);
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(conn, preStmt);
			}
		}
		if(logger.isInfoEnabled())
			logger.info("end save MO, result=" + result);
	}
	
	
	public static void saveMTtoQueue(String isdn, String content, String shortcode, String operator) {
		if(logger.isInfoEnabled())
			logger.info("begin sendSms, isdn=" + isdn + ", content [" + content + "]");

		Connection conn = null;
		PreparedStatement preStmt = null;
		String strSQL = null;
		// *
		try {
			
			conn = smsPool.getConnection();

			strSQL = "INSERT INTO SMS_SEND_QUEUE ( SENDER_NUMBER, RECEIVER_NUMBER, SERVICE_NUMBER, MOBILE_OPERATOR, COMMAND_CODE, CONTENT_TYPE, MESSAGE_TYPE, INFO, GEN_DATE, REQUEST_ID) "
					+ " VALUES (	 ?, 			?, 				?, 				?, 			'INV', 		0, 			0, 			?, 		now(), 0)";

			preStmt = conn.prepareStatement(strSQL);
			preStmt.setString(1, isdn);
			preStmt.setString(2, isdn);
			preStmt.setString(3, shortcode);
			preStmt.setString(4, operator);
			preStmt.setString(5, content);
			preStmt.executeUpdate();
		} catch (Exception e) {
			logger.error("Error executing SQL\n", e);
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(conn, preStmt);
			}
		}
		if(logger.isInfoEnabled())
			logger.info("end saveMTtoQueue, isdn=" + isdn + ", content [" + content + "]");
	}
	
	public static void savePendingRequest(SMSRequest mo, String serviceCode) {
		if(logger.isInfoEnabled())
			logger.info("begin savePendingRequest :" + mo + ", serviceCode" + serviceCode);
		
		
		Connection conn = null;
		PreparedStatement preStmt = null;
		int result = 0;
		try {
			
			conn = smsService.getConnection();

			String strSQL = "INSERT INTO LISA_PendingRequest"
					+ " 	(isdn, action, package, method, requestType, mo, service) "
					+ " VALUES (?, 	?, 		?, 		?, 		?, 			?, 		?)";

			preStmt = conn.prepareStatement(strSQL);
			preStmt.setString(1, mo.getSenderNumber());
			String action = mo.getInfo().equalsIgnoreCase("1") ||  mo.getInfo().equalsIgnoreCase("ON") ? "REG" :
				mo.getInfo().equalsIgnoreCase("0") ||  mo.getInfo().equalsIgnoreCase("OFF") ? "UNREG" : "HELP";
			preStmt.setString(2, action);
			preStmt.setString(3, "");
			preStmt.setString(4, "SMS");
			preStmt.setString(5, "SMS");
			preStmt.setString(6, mo.getInfo());
			preStmt.setString(7, serviceCode);
			
			preStmt.executeUpdate();
			preStmt.close();
			
		} catch (Exception e) {
			logger.error("Error executing SQL\n", e);
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(conn, preStmt);
			}
		}
		if(logger.isInfoEnabled())
			logger.info("end save MO, result=" + result);
	}

	public static ArrayList<SMSResponse> getMT(String operator, int maxrows) {
		logger.debug("get MT of " + operator);
		Connection conn = null;
		PreparedStatement preStmt = null;
		ArrayList<SMSResponse> re = new ArrayList<SMSResponse>();
		try {
			
			conn = smsPool.getConnection();

			String strSQL = "SELECT ID, SENDER_NUMBER, RECEIVER_NUMBER, SERVICE_NUMBER, MOBILE_OPERATOR, COMMAND_CODE, CONTENT_TYPE, IS_UNICODE, INFO, REQUEST_ID " 
				+ " FROM SMS_SEND_QUEUE where upper(MOBILE_OPERATOR) = upper(?) AND DELIVERY_STATUS in (?, ?) order by ID"
				+ " limit ?";

			preStmt = conn.prepareStatement(strSQL);
			preStmt.setString(1, operator);
			preStmt.setString(2, SMSResponse.DeliveryStatus.Waiting2Send);
			preStmt.setString(3, SMSResponse.DeliveryStatus.Waiting2Send);
			preStmt.setInt(4, maxrows);
			ResultSet rs = preStmt.executeQuery();
			if (rs.next()) {
				SMSResponse response = new SMSResponse();
				response.setId(rs.getInt("ID"));
				response.setSenderNumber(rs.getString("SENDER_NUMBER"));
				response.setReceiverNumber(rs.getString("RECEIVER_NUMBER"));
				response.setServiceNumber(rs.getString("SERVICE_NUMBER"));
				response.setMobileOperator(rs.getString("MOBILE_OPERATOR"));
				response.setContentType(rs.getInt("CONTENT_TYPE"));
				response.setInfo(rs.getString("INFO"));
				response.setRequestId(rs.getInt("REQUEST_ID"));
				response.setUnicode(rs.getBoolean("IS_UNICODE"));	
				re.add(response);
			}
			rs.close();
			preStmt.close();
		} catch (Exception e) {
			logger.error("Error executing SQL\n", e);
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(conn, preStmt);
			}
		}
		return re;
	}

	public static void updateMTStatusAndSeqById(int id, int seqNo, String submitting) {
		if(logger.isInfoEnabled())
			logger.info("begin udpate status of MT, MT'id " + id +   ",seqNo=" + seqNo + ", status: " + submitting);
		Connection conn = null;
		PreparedStatement preStmt = null;
		int result = 0;
		try {
			conn = smsPool.getConnection();
			String strSQL = "update SMS_SEND_QUEUE set DELIVERY_STATUS = ?, SEQ_NO = ? where ID = ?";
			preStmt = conn.prepareStatement(strSQL);
			preStmt.setString(1, submitting);
			preStmt.setInt(2, seqNo);
			preStmt.setInt(3, id);
			result = preStmt.executeUpdate();
			preStmt.close();
		} catch (Exception e) {
			logger.error("Error executing SQL\n", e);
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(conn, preStmt);
			}
		}
		if(logger.isInfoEnabled())
			logger.info("end updateMTStatusAndSeqById, id="+id + " result="+result);
	}

	public static void logMT(int seqNo) throws SQLException {
		if(logger.isInfoEnabled()){
			logger.info("begin: Log MT to database, seq no =" + seqNo);
		}
		SMSResponse res = getMTfromSeqNo(seqNo);
		
		Connection sqlCnn = smsPool.getConnection();
		PreparedStatement preStmt = null;
			
		if(res != null){

			String strSQL = "INSERT INTO SMS_SEND_LOG ( "
		        +" SENDER_NUMBER, RECEIVER_NUMBER, SERVICE_NUMBER, MOBILE_OPERATOR, COMMAND_CODE, "
		        +" CONTENT_TYPE, MESSAGE_TYPE, INFO, GEN_DATE, IS_UNICODE, SEQ_NO, DELIVERY_STATUS)" 
		        +" VALUES (?, ? , ?, ?, ?, ?, ?, ? , now(), ?, ?, ?)";

			preStmt = sqlCnn.prepareStatement(strSQL);
			preStmt.setString(1, res.getSenderNumber());
			preStmt.setString(2, res.getReceiverNumber());
			preStmt.setString(3, res.getServiceNumber());
			preStmt.setString(4, res.getMobileOperator());
			preStmt.setString(5, res.getCommandCode());
			preStmt.setInt(6, res.getContentType());
			preStmt.setInt(7, res.getMessageType());
			preStmt.setString(8, res.getInfo());
			preStmt.setBoolean(9, res.isUnicode());
			preStmt.setInt(10, res.getSeqNo());
			preStmt.setString(11, SMSResponse.DeliveryStatus.SentSuccess);
			preStmt.executeUpdate();
		}
		if(logger.isInfoEnabled()){
			logger.info("Finish: Log MT to database, seq no =" + seqNo);
		}
	}

	private static SMSResponse getMTfromSeqNo(int seqNo) throws SQLException {
		if(logger.isInfoEnabled()){
			logger.info("begin: Get MT from database, seq no =" + seqNo);
		}
		
		String strSQL = "SELECT ID, SENDER_NUMBER, RECEIVER_NUMBER, SERVICE_NUMBER, MOBILE_OPERATOR, COMMAND_CODE, CONTENT_TYPE, IS_UNICODE, INFO, SEQ_NO " 
			+ " FROM SMS_SEND_QUEUE where SEQ_NO = ?";
		Connection sqlCnn = smsPool.getConnection();
		PreparedStatement preStmt = null;
		SMSResponse response = null; //new SMSResponse();
		preStmt = sqlCnn.prepareStatement(strSQL);
		preStmt.setInt(1, seqNo);
		ResultSet rs = preStmt.executeQuery();
		if (rs.next()) {		
			response = new SMSResponse();
			response.setId(rs.getInt("ID"));
			response.setSenderNumber(rs.getString("SENDER_NUMBER"));
			response.setReceiverNumber(rs.getString("RECEIVER_NUMBER"));
			response.setServiceNumber(rs.getString("SERVICE_NUMBER"));
			response.setMobileOperator(rs.getString("MOBILE_OPERATOR"));
			response.setContentType(rs.getInt("CONTENT_TYPE"));
			response.setInfo(rs.getString("INFO"));
			response.setSeqNo(seqNo);
			response.setUnicode(rs.getBoolean("IS_UNICODE"));			
		}
		rs.close();
		preStmt.close();
		if(logger.isInfoEnabled()){
			logger.info("Finish: Get MT from database, seq no = " + response);
		}
		return response;
	}

	public static void logMO(SMSRequest mo) {
		if(logger.isInfoEnabled())
			logger.info("begin log MO :" + mo);
		Connection conn = null;
		PreparedStatement preStmt = null;
		int result = 0;
		try {
			
			conn = smsPool.getConnection();

			String strSQL = "INSERT INTO SMS_RECEIVE_LOG (SENDER_NUMBER, SERVICE_NUMBER,  MOBILE_OPERATOR, COMMAND_CODE, INFO, GEN_DATE, REQUEST_ID ) " 
							+ " VALUES(						?, 					?, 				?, 				?, 			?, 		now(), 	  ?)";

			preStmt = conn.prepareStatement(strSQL);
			preStmt.setString(1, mo.getSenderNumber());
			preStmt.setString(2, mo.getServiceNumber());
			preStmt.setString(3, mo.getMobileOperator());
			preStmt.setString(4, mo.getCommandCode());
			preStmt.setString(5, mo.getInfo());
			preStmt.setInt(6, mo.getRequestId());
			result = preStmt.executeUpdate();
		} catch (Exception e) {
			logger.error("Error executing SQL\n", e);
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(conn, preStmt);
			}
		}
		if(logger.isInfoEnabled())
			logger.info("end log MO, result=" + result);
	}

	public static void deleteMT(int seqNo, Connection sqlCnn) throws SQLException {
		logger.info("begin delete status of MT, MTseqNo " + seqNo );
		PreparedStatement preStmt = null;
		String strSQL = "delete from SMS_SEND_QUEUE where SEQ_NO = ? ";
		preStmt = sqlCnn.prepareStatement(strSQL);
		preStmt.setInt(1, seqNo);
		int re = preStmt.executeUpdate();
		preStmt.close();
		logger.info("end delete status of MT, result = " + re );
	}
	
	public static void deleteMT(int seqNo) throws SQLException {
		logger.info("begin delete status of MT, MTseqNo " + seqNo );
		Connection sqlCnn = smsPool.getConnection();
		PreparedStatement preStmt = null;
		String strSQL = "delete from SMS_SEND_QUEUE where SEQ_NO = ? ";
		preStmt = sqlCnn.prepareStatement(strSQL);
		preStmt.setInt(1, seqNo);
		int re = preStmt.executeUpdate();
		preStmt.close();
		logger.info("end delete status of MT, result = " + re );
	}

	public static void updateMTStatus(int seqNo, String sentfail) {
		logger.info("udpate status of MT, seqNo " + seqNo + ", status: " + sentfail);
		Connection conn = smsPool.getConnection();
		PreparedStatement preStmt = null;
		try {
			conn = smsPool.getConnection();
			String strSQL = "update SMS_SEND_QUEUE set DELIVERY_STATUS = ? where SEQ_NO = ?";
			preStmt = conn.prepareStatement(strSQL);
			preStmt.setString(1, sentfail);
			preStmt.setInt(2, seqNo);
			preStmt.executeUpdate();
			preStmt.close();
		} catch (Exception e) {
			logger.error("Error executing SQL\n", e);
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(conn, preStmt);
			}
		}
	}

	public static void saveMOtoLog(SMSRequest mo) {
		logger.info("save MO to log table:" + mo.toString());
		Connection conn = null;
		PreparedStatement preStmt = null;
		try {
			
			conn = smsPool.getConnection();
			String strSQL = "INSERT INTO SMS_RECEIVE_LOG (SENDER_NUMBER, SERVICE_NUMBER, MOBILE_OPERATOR, COMMAND_CODE, INFO, GEN_DATE, REQUEST_ID) "
								+ " VALUES (					?, 				?, 				?, 				?, 			?, 		now(), 	  0)";

			preStmt = conn.prepareStatement(strSQL);
			preStmt.setString(1, mo.getSenderNumber());
			preStmt.setString(2, mo.getServiceNumber());
			preStmt.setString(3, mo.getMobileOperator());
			preStmt.setString(4, mo.getCommandCode());
			preStmt.setString(5, mo.getInfo());
			preStmt.executeUpdate();
			
		} catch (Exception e) {
			logger.error("Error executing SQL\n", e);
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(conn, preStmt);
			}
		}
	}

	public static void saveMT(ArrayList<SMSResponse> responses) {
		logger.info("save MT to SMS_SEND_QUEUE table");
		Connection conn = null;
		PreparedStatement preStmt = null;
		try {
			conn = smsPool.getConnection();
			String strSQL = "INSERT INTO SMS_SEND_QUEUE (SENDER_NUMBER, RECEIVER_NUMBER, SERVICE_NUMBER, "
				+ " MOBILE_OPERATOR, COMMAND_CODE, CONTENT_TYPE, MESSAGE_TYPE, INFO, "
				+ " GEN_DATE, REQUEST_ID, IS_UNICODE, DELIVERY_STATUS, SEQ_NO) "
				+ " VALUES  (					?, 				?, 				?, "
				+ " 	?, 			   ?, 			?, 	  			?,			?," 
				+ " 	now(), 		0, 			?, 	  			?,			?)";
			for (SMSResponse mt : responses) {
				logger.info("Begin save MT to send" + mt);
				preStmt = conn.prepareStatement(strSQL);
				preStmt.setString(1, mt.getSenderNumber());
				preStmt.setString(2, mt.getReceiverNumber());
				preStmt.setString(3, mt.getServiceNumber());
				preStmt.setString(4, mt.getMobileOperator());
				preStmt.setString(5, mt.getCommandCode());
				preStmt.setInt(6, mt.getContentType());
				preStmt.setInt(7, mt.getMessageType());
				preStmt.setString(8, mt.getInfo());
				preStmt.setBoolean(9, mt.isUnicode());
				preStmt.setString(10, SMSResponse.DeliveryStatus.Waiting2Send);
				preStmt.setInt(11, mt.getSeqNo());
				preStmt.executeUpdate();
				preStmt.close();
			}
		} catch (Exception e) {
			logger.error("Error executing SQL\n", e);
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(conn, preStmt);
			}
		}
	}
	
	public static void saveMT_Metfone(String isdn, String content, String serviceNo) {
		logger.info("save Metfone MT to SMS_SEND_QUEUE table");
		Connection conn = null;
		PreparedStatement preStmt = null;
		try {
			conn = smsPool.getConnection();
			String strSQL = "INSERT INTO SMS_SEND_QUEUE (SENDER_NUMBER, RECEIVER_NUMBER, SERVICE_NUMBER, "
				+ " MOBILE_OPERATOR, COMMAND_CODE, CONTENT_TYPE, MESSAGE_TYPE, INFO, "
				+ " GEN_DATE, REQUEST_ID, IS_UNICODE, SEQ_NO) "
				+ " VALUES  (					?, 				?, 				?, "
				+ " 	'Metfone', 			   'SYS', 			0, 	  			0,			?," 
				+ " 	now(), 		0, 			1, 	  		0)";
			
				logger.info("Begin save MT to send: " + content);
				preStmt = conn.prepareStatement(strSQL);
				preStmt.setString(1, isdn);
				preStmt.setString(2, isdn);
				preStmt.setString(3, serviceNo);
				preStmt.setString(4, content);
				preStmt.executeUpdate();
				preStmt.close();
			
		} catch (Exception e) {
			logger.error("Error executing SQL\n", e);
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(conn, preStmt);
			}
		}
	}

	public static int getStartSEQ_NO(String operator) {
		logger.info("getStartSEQ_NO of " + operator);
		Connection conn = null;
		PreparedStatement preStmt = null;
		int re = 1;
		try {
			
			conn = smsPool.getConnection();

			String strSQL = "Select max(SEQ_NO) from SMS_SEND_LOG where upper(MOBILE_OPERATOR) = upper(?)";

			preStmt = conn.prepareStatement(strSQL);
			preStmt.setString(1, operator);
			ResultSet rs = preStmt.executeQuery();
			if (rs.next()) {
				re = rs.getInt(1);
			}
			rs.close();
			preStmt.close();
		} catch (Exception e) {
			logger.error("Error executing SQL\n", e);
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(conn, preStmt);
			}
		}
		logger.info("the StartSEQ_NO is: " + (re + 1));
		return re + 1;
	}

	public static void mark4Send(ArrayList<SMSResponse> mts) {
		String ids = "";
		if(mts == null || mts.size() == 0) {
			logger.debug("No Mts to mark");
			return;
		}
		for (SMSResponse mt : mts) {
			ids += "," + mt.getId();
		}
		ids = ids.substring(1);
		if(logger.isDebugEnabled())
			logger.debug("begin mark4Send, MT'ids " + ids);
		Connection conn = null;
		PreparedStatement preStmt = null;		
		try {
			conn = smsPool.getConnection();
			String strSQL = "update SMS_SEND_QUEUE set DELIVERY_STATUS = 'MARKED' where ID in (" + ids +")";
			preStmt = conn.prepareStatement(strSQL);			
			preStmt.executeUpdate();
			preStmt.close();
		} catch (Exception e) {
			logger.error("Error executing SQL ", e);
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(conn, preStmt);
			}
		}
		if(logger.isInfoEnabled())
			logger.info("end mark4Send, MT'ids " + ids);
	}
}
